package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.ITradCreditLmtDefDao;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.TradCreditLmtDef;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class TradCreditLmtDefDaoImpl extends BaseDaoImpl implements
		ITradCreditLmtDefDao {
	private static Logger logger = LogUtil.getLog(TradCreditLmtDefDaoImpl.class);
	@Override
	public Page queryByChannelName(Integer pageIndex, String channelName,GatewayUser user)
			throws Exception {
		String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		Long total = 0L;
		Page creditLmtDefPage = null;
		
//		String sqlbase = "SELECT TEM_TRACE_3.LIMIT_NO,                                                     "
//				+"       TEM_TRACE_3.CHANNEL_CODE,                                                 "
//				+"       TEM_TRACE_3.CHANNEL_NAME,                                                 "
//				+"       TEM_TRACE_3.ALERT_LMT,                                                    "
//				+"       TEM_TRACE_3.TERMINAL_LMT,                                                 "
//				+"       ROUND(NVL(T3.SAVE_MONEY, 0), 2) SAVE_MONEY,                               "
//				+"       ROUND(NVL(TEM_TRACE_3.TOTAL - T3.SAVE_MONEY, 0), 2) AS SUM_APPEND_MONEY   "
//				+"  FROM (SELECT (SELECT MAX(SERIAL_NO)                                            "
//				+"                  FROM T_TRAD_CREDIT_LMT_TRACE T                                 "
//				+"                 WHERE T.LIMIT_NO = D.LIMIT_NO) MAX_SERIAL,                      "
//				+"               D.LIMIT_NO,                                                       "
//				+"               B.CHANNEL_NAME,                               "
//				+"               B.CHANNEL_CODE,                               "
//				+"               ROUND(NVL(D.ALERT_LMT, 0), 2) ALERT_LMT,                          "
//				+"               ROUND(NVL(D.TERMINAL_LMT, 0), 2) TERMINAL_LMT,                    "
//				+"               TEM_TRACE_2.TOTAL,                                                "
//				+"               D.UPDATE_TIME                                                     "
//				+"          FROM T_TRAD_CREDIT_LMT_DEF D                                           "
//				+"         INNER JOIN ( SELECT N.CHANNEL_CODE, N.CHANNEL_NAME	"
//				+"		  FROM T_GATEWAY_CHANNEL_INFO N "
//				+"		 INNER JOIN (SELECT DISTINCT P.DEPT_CODE"
//				+"		               FROM T_CORE_DEPT P"
//				+"		              WHERE LENGTH(P.DEPT_CODE) = 2"
//				+"		             CONNECT BY PRIOR P.DEPT_CODE = P.SNR_DEPT"
//				+"		              START WITH P.DEPT_CODE IN ('"+deptStr+"')) DEPT_TEM"
//				+"		    ON DEPT_TEM.DEPT_CODE = N.DEPT_CODE"
//				+"		   AND N.STATUS = '1') B                                     "
//				+"            ON D.PARTNER_CODE = B.CHANNEL_CODE"
//				+ "               AND D.STATUS = '1'"
//				+"         left JOIN (SELECT T.LIMIT_NO, SUM(T.APPEND_MONEY) AS TOTAL             "
//				+"                      FROM T_TRAD_CREDIT_LMT_TRACE T                             "
//				+"                     WHERE T.APPEND_TYPE = '03'                                  "
//				+"                     GROUP BY T.LIMIT_NO) TEM_TRACE_2                            "
//				+"            ON D.LIMIT_NO = TEM_TRACE_2.LIMIT_NO) TEM_TRACE_3                    "
//				+" left JOIN T_TRAD_CREDIT_LMT_TRACE T3                                           "
//				+"    ON T3.SERIAL_NO = TEM_TRACE_3.MAX_SERIAL                                     "
//				+" WHERE 1 = 1                                                                     ";
		
		String sqlbase = "SELECT TEM_TRACE_3.LIMIT_NO,\n" +
							"       TEM_TRACE_3.CHANNEL_CODE,\n" + 
							"       TEM_TRACE_3.CHANNEL_NAME,\n" + 
							"       TEM_TRACE_3.ALERT_LMT,\n" + 
							"       TEM_TRACE_3.TERMINAL_LMT,\n" + 
							"       ROUND(NVL(T3.SAVE_MONEY, 0), 2) SAVE_MONEY,\n" + 
							"       ROUND(NVL(NVL(TEM_TRACE_3.TOTAL, 0) - T3.SAVE_MONEY, 0), 2) AS SUM_APPEND_MONEY,\n" + 
							"       TEM_TRACE_3.SELF_ONLY ,TEM_TRACE_3.PARENT_CHANNEL_CODE " + 
							"  FROM (SELECT (SELECT MAX(SERIAL_NO)\n" + 
							"                  FROM T_TRAD_CREDIT_LMT_TRACE T\n" + 
							"                 WHERE T.LIMIT_NO = D.LIMIT_NO) MAX_SERIAL,\n" + 
							"               D.LIMIT_NO,\n" + 
							"               B.CHANNEL_NAME,\n" + 
							"               B.CHANNEL_CODE,\n" + 
							"               ROUND(NVL(D.ALERT_LMT, 0), 2) ALERT_LMT,\n" + 
							"               ROUND(NVL(D.TERMINAL_LMT, 0), 2) TERMINAL_LMT,\n" + 
							"               TEM_TRACE_2.TOTAL,\n" + 
							"               D.UPDATE_TIME,\n" + 
							"               D.SELF_ONLY,\n" + 
							"               B.PARENT_CHANNEL_CODE\n" + 
							"          FROM T_TRAD_CREDIT_LMT_DEF D\n" + 
							"         INNER JOIN (SELECT sub.parent_channel_code,sub.sub_channel_code channel_code,sub.sub_channel_name channel_name FROM t_gateway_sub_channel sub\n" + 
							"                             WHERE sub.status = '1'\n" + 
							"                             CONNECT BY NOCYCLE PRIOR sub.sub_channel_code = sub.parent_channel_code\n" + 
							"                             START WITH sub.sub_channel_code IN(\n" + 
							"                                SELECT N.CHANNEL_CODE\n" + 
							"                                  FROM T_GATEWAY_CHANNEL_INFO N\n" + 
							"                                 INNER JOIN (SELECT DISTINCT P.DEPT_CODE\n" + 
							"                                              FROM T_CORE_DEPT P\n" + 
							"                                             WHERE LENGTH(P.DEPT_CODE) = 2\n" + 
							"                                            CONNECT BY PRIOR P.DEPT_CODE = P.SNR_DEPT\n" + 
							"                                             START WITH P.DEPT_CODE IN ('"+deptStr+"')) DEPT_TEM\n" + 
							"                                    ON DEPT_TEM.DEPT_CODE = N.DEPT_CODE\n" + 
							"                                   AND N.STATUS = '1')) B\n" + 
							"            ON D.PARTNER_CODE = B.CHANNEL_CODE\n" + 
							"           AND D.STATUS = '1'\n" + 
							"          LEFT JOIN (SELECT T.LIMIT_NO, SUM(T.APPEND_MONEY) AS TOTAL\n" + 
							"                      FROM T_TRAD_CREDIT_LMT_TRACE T\n" + 
							"                     WHERE T.APPEND_TYPE = '03'\n" + 
							"                     GROUP BY T.LIMIT_NO) TEM_TRACE_2\n" + 
							"            ON D.LIMIT_NO = TEM_TRACE_2.LIMIT_NO) TEM_TRACE_3\n" + 
							"  LEFT JOIN T_TRAD_CREDIT_LMT_TRACE T3\n" + 
							"    ON T3.SERIAL_NO = TEM_TRACE_3.MAX_SERIAL\n" + 
							" WHERE 1 = 1";

		
 		StringBuilder sql = new StringBuilder();
		sql.append(sqlbase);
		
		String channelNameStr = "%"+channelName+"%";
		if(StrUtil.isNotEmpty(channelName)){
			sql.append(" and TEM_TRACE_3.channel_name like ?");
		}
		
		sql.append(" order by TEM_TRACE_3.update_time desc");
		
		logger.info("额度控制查询SQL");
		logger.info(sql);
		try{
			conn = this.getConnection();
			pstmt = conn.prepareStatement("select count(*) from ("+sql.toString()+")");
			
			if(StrUtil.isNotEmpty(channelName)){
				pstmt.setString(1, channelNameStr);
			}
			
			rs = pstmt.executeQuery();
			if(rs.next()){
				total = rs.getLong(1);
			}
			
			creditLmtDefPage = new Page(pageIndex,total); 
			
			SqlUtil sqlUtil = new SqlUtil();
			
			String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(), creditLmtDefPage).toString();
			
			pstmt1 = conn.prepareStatement(sqlPage);
			if(StrUtil.isNotEmpty(channelName)){
				pstmt1.setString(1, channelNameStr);
			}
			
			rs = pstmt1.executeQuery();
			
			List creditLmtDescList = DBUtil.transRs2List(rs);
			creditLmtDefPage.setData(creditLmtDescList);
		
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		return creditLmtDefPage;
	}

	@Override
	public void save(TradCreditLmtDef creditLmtDef) throws Exception {
			
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select nvl(max(limit_no+1),0) from t_trad_credit_lmt_def";
		try{
			conn = this.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				Integer seq = rs.getInt(1);
				
				StringBuilder limitNoBase = null;
				
				if(seq != 0){
					limitNoBase = new StringBuilder(seq.toString());
				}else{
					limitNoBase = new StringBuilder("1");
				}
				
				StringBuilder limitNo = new StringBuilder();

				for(int i=0;i<10 - limitNoBase.length();i++){
					limitNo.append("0");
				}
				limitNo.append(limitNoBase);
				creditLmtDef.setLimitNo(limitNo.toString());
			}
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
			this.add("insertTradCreditLmtDef", creditLmtDef);
	}

	@Override
	public void update(TradCreditLmtDef creditLmtDef) throws Exception {
		
		this.update("updateTradCreditLmtDefNotNull", creditLmtDef);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TradCreditLmtDef> queryTradCreditLmtDefsByChannelCode(
			String channelCode) throws Exception {
		return this.queryList("queryTradCreditLmtDefsByChannelCode", channelCode);
	}

	@Override
	public List<TradCreditLmtDef> queryTradCreditLmtDefs(
			TradCreditLmtDef creditLmtDef) throws Exception {
		return this.queryList("queryTradCreditLmtDefByMap", creditLmtDef);
	}

}
